package cs236369.hw5;

import java.sql.Array;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

/**
 * This class handles the connections to the databases
 */
public abstract class DBConnection {

	// ## local variables ##
	static final int transactionRetriesMaxNumber = 10;		// maximum number of retries a transaction
															// will try before throwing SQLException

	/*
	 * addUser comment
	 * ---------------
	 * Although we need to check that the user doesn't exist in the database, and it might be created
	 * by another transaction during the running of this transaction, this method doesn't acquire a lock,
	 * because we don't want to lock the whole table (if we lock- no other user can be added or removed
	 * while this transaction run). We can do that because we define username as a primary key of table 
	 * users, therefore postgreSQL ensures us that if a username is added twice, we will get an exception,
	 * and the transaction will not succeed.
	 */
	/**
	 * Adds a user to the database
	 * @param user - the user to add
	 * @throws RescueMeException if the user is not legal (missing basic information)
	 * @throws SQLException if SQL query fails
	 */
	public static void addUser(User user) throws RescueMeException {
		PreparedStatement statement = null;
		ResultSet rs = null;
		Connection connection = null;

		try {
			connection = InitListener.getConnection();
			connection.setAutoCommit(false);
			
			if(user.getUserName().isEmpty() || user.getPassword().isEmpty()) {
				throw new RescueMeException("missingUserInfo", "User is missing username or password");
			}
			//start transaction
			connection.createStatement().execute("begin");
			//check the user is not in the database and lock the row so it won't be deleted
			statement = connection.prepareStatement("SELECT username FROM users WHERE username=? FOR UPDATE");
			statement.setString(1, user.getUserName());
			rs = statement.executeQuery();
			if (rs.next() != false) {
				throw new RescueMeException("userExists", "Username is taken");
			}
			// prepare statement string
			String queryTemplate = "INSERT INTO users(username, password, firstname, lastname, email, phone, isAdmin)" + 
						"VALUES(?, ?, ?, ?, ?, ?, ?)";
			statement = connection.prepareStatement(queryTemplate);
			statement.setString(1, user.getUserName());
			statement.setString(2, user.getPassword());
			statement.setString(3, user.getFirstName());
			statement.setString(4, user.getLastName());
			statement.setString(5, user.getEmail());
			statement.setString(6, user.getPhone());
			statement.setBoolean(7, user.isAdmin());
			//commit changes
			statement.executeUpdate();
			connection.createStatement().execute("commit");
		} catch (SQLException e) {
			try {
				connection.createStatement().execute("rollback");
			} catch (SQLException e1) { e1.printStackTrace(); }
			e.printStackTrace();
			throw new RescueMeException("SQLException", sqlerr);
		} finally {
			try { if(rs != null) { rs.close(); } } catch (SQLException e) { e.printStackTrace(); }
			try { if(statement != null) { statement.close(); } } catch (SQLException e) { e.printStackTrace(); }
			try { if(connection != null) { connection.close(); } } catch (SQLException e) { e.printStackTrace(); }
		}
	}
	
	
	/**
	 * Adds a mission (task) to the database
	 * @param mission - the mission to add
	 * @return the mission's ID as generated by the database. Returns -1 if an SQLException occurred
	 * @throws RescueMeException if the user that posted the mission doesn't exist anymore or if one of
	 * the skills is not valid (not in the DB) or not available
	 */
	public static int addMission(Mission mission) throws RescueMeException {
		if(mission == null) {
			throw new RescueMeException("nullParameter", "Mission is an illegal null parameter"); 
		}
		PreparedStatement statement1 = null;
		PreparedStatement statement2 = null;
		ResultSet rs = null;
		Connection connection = null;
		int missionID = -1;
		try {
			connection = InitListener.getConnection();
			connection.setAutoCommit(false);
			ArrayList<Object> skillIDs = new ArrayList<Object>();
			//start transaction
			connection.createStatement().execute("begin");
			/*
			 * When we create a mission, we must verify the user that posted the mission exists, and 
			 * also lock the row of the user in the users table so that it won't be deleted during 
			 * the transaction.
			 * SELECT FOR SHARE locks the selection for writes, but enables multiple reads.  
			 */
			statement1 = connection.prepareStatement("SELECT username FROM users WHERE username=? FOR SHARE");
			statement1.setString(1, mission.getOwner());
			rs = statement1.executeQuery();
			if(rs.next() == false) { // username doesn't exists
				connection.createStatement().execute("rollback");
				throw new RescueMeException("userNotExists", "The user that posted this mission doesn't exist anymore");
			}
			rs.close();
			rs = null;
			
			// create array of only available skills
			if(mission.getSkills().length > 0) {
				ArrayList<Skill> availableSkills = getSkills(true);
				for(Skill s : mission.getSkills()) {
					if(Skill.findSkillById(availableSkills, s.getId()) != null) {
						skillIDs.add(s.getId());
					}
				}
			}
			Array skillsIDArray = connection.createArrayOf("int4", skillIDs.toArray());

			// get the ID of the new message from the database
			rs = connection.createStatement().executeQuery("SELECT nextval('tasks_taskid_seq')");
			rs.next();
			missionID = rs.getInt(1);
			// insert message query
			statement2 = connection.prepareStatement("INSERT INTO tasks(taskid, ownername, creationtime, taskname," +
					"capacity, content, geotag, latnum, longnum, skills) VALUES(" + missionID + ",?,?,?,?,?," +
					"ST_GeographyFromText('POINT(" + mission.getLongitude() + " " + mission.getLatitute() + ")'),?,?,?)");			
			// populate values
			statement2.setString(1, mission.getOwner());
			statement2.setDate(2, mission.getCreationTime());	
			statement2.setString(3, mission.getName());
			statement2.setInt(4, mission.getCapacity());	
			statement2.setString(5, mission.getContent());
			statement2.setDouble(6, mission.getLatitute());
			statement2.setDouble(7, mission.getLongitude());
			statement2.setArray(8, skillsIDArray);
			
			// execute and commit changes
			statement2.executeUpdate();
			connection.createStatement().execute("commit");
			return missionID;
		} catch (SQLException e) {
			try {
				connection.createStatement().execute("rollback");
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
			throw new RescueMeException("SQLException", sqlerr);
		} finally {
			try { if(rs != null) { rs.close(); } } catch (SQLException e) { e.printStackTrace(); }
			try { if(statement1 != null) { statement1.close(); } } catch (SQLException e) { e.printStackTrace(); }
			try { if(statement2 != null) { statement2.close(); } } catch (SQLException e) { e.printStackTrace(); }
			try { if(connection != null) { connection.close(); } } catch (SQLException e) { e.printStackTrace(); }
		}
	}
	
	/**
	 * Adds a team to the database
	 * @param team - the team to add
	 * @return the team's ID as generated by the database. Returns -1 if an SQLException occurred
	 * @throws RescueMeException if the user that posted the team doesn't exist anymore or if one of
	 * the skills is not valid (not in the DB) or not available
	 */
	public static int addTeam(Team team) throws RescueMeException {
		if(team == null) {
			throw new RescueMeException("nullParameter", "Team is an illegal null parameter"); 
		}
		PreparedStatement statement1 = null;
		PreparedStatement statement2 = null;
		ResultSet rs = null;
		Connection connection = null;
		int teamId = -1;
		
		try {
			connection = InitListener.getConnection();
			connection.setAutoCommit(false);
			ArrayList<Object> skillIDs = new ArrayList<Object>();
			//start transaction
			connection.createStatement().execute("begin");
			/*
			 * When we create a team, we must verify the user that posted the team exists, and 
			 * also lock the row of the user in the users table so that it won't be deleted during 
			 * the transaction.
			 * SELECT FOR SHARE locks the selection for writes, but enables multiple reads.  
			 */
			statement1 = connection.prepareStatement("SELECT username FROM users WHERE username=? FOR SHARE");
			statement1.setString(1, team.getOwner());
			rs = statement1.executeQuery();
			if(rs.next() == false) { // username doesn't exists
				connection.createStatement().execute("rollback");
				throw new RescueMeException("illegal parameter","The user that posted this team doesn't exist anymore");
			}
			rs.close();
			rs = null;
			statement1.close();
			statement1 = null;
			
			// create array of only available skills
			if(team.getSkills().length > 0) {
				ArrayList<Skill> availableSkills = getSkills(true);
				for(Skill s : team.getSkills()) {
					if(Skill.findSkillById(availableSkills, s.getId()) != null) {
						skillIDs.add(s.getId());
					}
				}
			}
			Array skillsIDArray = connection.createArrayOf("int4", skillIDs.toArray());

			// get the ID of the new message from the database
			rs = connection.createStatement().executeQuery("SELECT nextval('teams_teamid_seq')");
			rs.next();
			teamId = rs.getInt(1);
			
			// insert message query
			statement2 = connection.prepareStatement("INSERT INTO teams(teamid, ownername, creationtime, teamname, " +
					"capacity, content, geotag, latnum, longnum, skills, team_limit) " +
					"VALUES(" + teamId + ",?,?,?,?,?,ST_GeographyFromText('POINT(" + team.getLongitude() + 
					" " + team.getLatitute() + ")'),?,?,?,?)");
			// populate values
			statement2.setString(1, team.getOwner());
			statement2.setDate(2, team.getCreationTime());	
			statement2.setString(3, team.getName());
			statement2.setInt(4, team.getCapacity());	
			statement2.setString(5, team.getContent());
			statement2.setDouble(6, team.getLatitute());
			statement2.setDouble(7, team.getLongitude());
			statement2.setArray(8, skillsIDArray);
			statement2.setInt(9, team.getLimit());
			statement2.executeUpdate();
			// add the owner to the team members
			statement1 = connection.prepareStatement("INSERT INTO user_in_team(teamid, username) " +
					"VALUES(" + teamId + ",?)");
			statement1.setString(1, team.getOwner());
			statement1.executeUpdate();
			connection.createStatement().execute("commit");
			return teamId;
		} catch (SQLException e) {
			try {
				connection.createStatement().execute("rollback");
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
			throw new RescueMeException("SQLException", sqlerr);
		} finally {
			try { if(rs != null) { rs.close(); } } catch (SQLException e) { e.printStackTrace(); }
			try { if(statement1 != null) { statement1.close(); } } catch (SQLException e) { e.printStackTrace(); }
			try { if(statement2 != null) { statement2.close(); } } catch (SQLException e) { e.printStackTrace(); }
			try { if(connection != null) { connection.close(); } } catch (SQLException e) { e.printStackTrace(); }
		}
	}

	/**
	 * Adds a skill to the database
	 * @param user - the user to add
	 * @throws RescueMeException if the user is not legal (missing basic information)
	 * @throws SQLException if SQL query fails
	 */
	public static int addSkill(String skill) throws RescueMeException {
		if(skill == null || skill.isEmpty()) {
			throw new RescueMeException("illegal input", "skill is empty");
		}
		PreparedStatement statement = null;
		ResultSet rs = null;
		Connection connection = null;
		int newId = -1;
		try {
			connection = InitListener.getConnection();
			connection.setAutoCommit(false);
			//start transaction
			connection.createStatement().execute("begin");
			//check the skill is not in the database and lock the row so it won't be deleted
			statement = connection.prepareStatement("SELECT sid FROM skills WHERE skillname=? FOR UPDATE");
			statement.setString(1, skill);
			rs = statement.executeQuery();
			if (rs.next() != false) {
				throw new RescueMeException("illegal input", "Skill already exists");
			}
			rs.close();
			rs = null;
			statement.close();
			statement = null;
			// prepare statement string
			statement = connection.prepareStatement("INSERT INTO skills(skillname, available) VALUES(?, ?)");
			statement.setString(1, skill);
			statement.setBoolean(2, true);
			statement.executeUpdate();
			statement = connection.prepareStatement("Select sid FROM skills WHERE skillname=?");
			statement.setString(1, skill);
			rs = statement.executeQuery();
			if (rs.next() != false) { newId = rs.getInt("sid"); }
			connection.createStatement().execute("commit");
		} catch (SQLException e) {
			try {
				connection.createStatement().execute("rollback");
			} catch (SQLException e1) { e1.printStackTrace(); }
			throw new RescueMeException("SQLException", sqlerr);
		} finally {
			try { if(rs != null) { rs.close(); } } catch (SQLException e) { e.printStackTrace(); }
			try { if(statement != null) { statement.close(); } } catch (SQLException e) { e.printStackTrace(); }
			try { if(connection != null) { connection.close(); } } catch (SQLException e) { e.printStackTrace(); }
		}
		return newId;
	}	
	
	/**
	 * Deletes a user from the database
	 * @param userName - the user to delete
	 * @throws RescueMeException - if user is empty or null
	 * @throws SQLException if SQL query fails
	 */
	public static void delUser(String userName) throws RescueMeException, SQLException {
		if(null==userName || userName.isEmpty()) {
			throw new RescueMeException("emptyString", "User name is empty or null");
		}
		PreparedStatement statement = null;
		Connection connection = null;
		try {
			connection = InitListener.getConnection();
			connection.setAutoCommit(false);
			// Delete user.
			// Note: the messages, events and registrations of the deleted user are deleted automatically
			// by postgres because we added foreign keys in the tables and added ON DELETE CASCADE.
			statement = connection.prepareStatement("DELETE FROM users WHERE username=?");
			statement.setString(1, userName);
			statement.executeUpdate();
			connection.createStatement().execute("commit");
		} finally {
			try { statement.close(); }
			catch (SQLException e) { e.printStackTrace(); }
			if(null!=connection) { try { connection.close(); } catch (SQLException e) { e.printStackTrace(); } }
		}
	}
	
	/**
	 * updates a mission (task) on the database
	 * @param mission the mission to update
	 * @throws RescueMeException if the user that posted the mission doesn't exist anymore or if one of
	 * the skills is not valid (not in the DB) or not available
	 */
	public static void updateMission(Mission mission) throws RescueMeException {
		if(mission == null || mission.getOwner() == null) {
			throw new RescueMeException("nullParameter", "Mission or updater is an illegal null parameter"); 
		}
		PreparedStatement statement1 = null;
		PreparedStatement statement2 = null;
		ResultSet rs = null;
		Connection connection = null;
		try {
			connection = InitListener.getConnection();
			connection.setAutoCommit(false);
			ArrayList<Object> skillIDs = new ArrayList<Object>();
			//start transaction
			connection.createStatement().execute("begin");
			statement1 = connection.prepareStatement("SELECT ownername FROM tasks WHERE taskid=? FOR UPDATE");
			statement1.setInt(1, mission.getId());
			rs = statement1.executeQuery();
			if(rs.next() == false) { // task doesn't exists
				connection.createStatement().execute("rollback");
				throw new RescueMeException("taskNotExists", "The task doesn't exist anymore");
			}
			String taskOwner = rs.getString("ownername");
			if(!taskOwner.equals(mission.getOwner())) {
				connection.createStatement().execute("rollback");
				throw new RescueMeException("unauthorized","User " + mission.getOwner() + " is not authorized to update this task");
			}
			rs.close();
			rs = null;
			
			if(mission.getSkills().length > 0) {
				ArrayList<Skill> availableSkills = getSkills(false);
				for(Skill s : mission.getSkills()) {
					if(Skill.findSkillById(availableSkills, s.getId()) != null) {
						skillIDs.add(s.getId());
					}
				}
			}
			Array skillsIDArray = connection.createArrayOf("int4", skillIDs.toArray());
			
			// insert message query
			statement2 = connection.prepareStatement("UPDATE tasks SET capacity=?, content=?, skills=? WHERE taskid=?");
			// populate values
			statement2.setInt(1, mission.getCapacity());
			statement2.setString(2, mission.getContent());
			statement2.setArray(3, skillsIDArray);
			statement2.setInt(4, mission.getId());
			// execute and commit changes
			statement2.executeUpdate();
			connection.createStatement().execute("commit");
		} catch (SQLException e) {
			try { connection.createStatement().execute("rollback"); }
			catch (SQLException e1) { e1.printStackTrace(); }
			e.printStackTrace();
			throw new RescueMeException("SQLException", sqlerr);
		} finally {
			try { if(rs != null) { rs.close(); } } catch (SQLException e) { e.printStackTrace(); }
			try { if(statement1 != null) { statement1.close(); } } catch (SQLException e) { e.printStackTrace(); }
			try { if(statement2 != null) { statement2.close(); } } catch (SQLException e) { e.printStackTrace(); }
			try { if(connection != null) { connection.close(); } } catch (SQLException e) { e.printStackTrace(); }
		}
	}
	
	/**
	 * updates a team on the database
	 * @param team the team to update
	 * @throws RescueMeException if the user that posted the mission doesn't exist anymore or if one of
	 * the skills is not valid (not in the DB) or not available
	 */
	public static void updateTeam(Team team) throws RescueMeException {
		if(team == null || team.getOwner() == null) {
			throw new RescueMeException("nullParameter", "team or updater is an illegal null parameter"); 
		}
		PreparedStatement statement1 = null;
		PreparedStatement statement2 = null;
		ResultSet rs = null;
		Connection connection = null;
		try {
			connection = InitListener.getConnection();
			connection.setAutoCommit(false);
			ArrayList<Object> skillIDs = new ArrayList<Object>();
			connection.createStatement().execute("begin");
			statement1 = connection.prepareStatement("SELECT ownername FROM teams WHERE teamid=? FOR UPDATE");
			statement1.setInt(1, team.getId());
			rs = statement1.executeQuery();
			if(rs.next() == false) { // team doesn't exists
				connection.createStatement().execute("rollback");
				throw new RescueMeException("teamNotExists","The team doesn't exist anymore");
			}
			String taskOwner = rs.getString("ownername");
			if(!taskOwner.equals(team.getOwner())) {
				connection.createStatement().execute("rollback");
				throw new RescueMeException("unauthorized","User " + team.getOwner() + "is not authorized to update this team");
			}
			rs.close();
			rs = null;
			
			if(team.getSkills().length > 0) {
				ArrayList<Skill> availableSkills = getSkills(false);
				for(Skill s : team.getSkills()) {
					if(Skill.findSkillById(availableSkills, s.getId()) != null) {
						skillIDs.add(s.getId());
					}
				}
			}
			Array skillsIDArray = connection.createArrayOf("int4", skillIDs.toArray());
			
			// insert message query
			statement2 = connection.prepareStatement("UPDATE teams SET capacity=?, content=?, skills=?, team_limit=? WHERE teamid=?");
			// populate values
			statement2.setInt(1, team.getCapacity());
			statement2.setString(2, team.getContent());
			statement2.setArray(3, skillsIDArray);
			statement2.setInt(4, team.getLimit());
			statement2.setInt(5, team.getId());
			// execute and commit changes
			statement2.executeUpdate();
			connection.createStatement().execute("commit");
		} catch (SQLException e) {
			try { connection.createStatement().execute("rollback"); }
			catch (SQLException e1) { e1.printStackTrace(); }
			e.printStackTrace();
			throw new RescueMeException("SQLException", sqlerr);
		} finally {
			try { if(rs != null) { rs.close(); } } catch (SQLException e) { e.printStackTrace(); }
			try { if(statement1 != null) { statement1.close(); } } catch (SQLException e) { e.printStackTrace(); }
			try { if(statement2 != null) { statement2.close(); } } catch (SQLException e) { e.printStackTrace(); }
			try { if(connection != null) { connection.close(); } } catch (SQLException e) { e.printStackTrace(); }
		}
	}
	
	/**
	 * Deletes a post from the database
	 * @param id the id of the post to delete
	 * @param postType - 0: Mission; 1: task
	 * @throws RescueMeException - if team is empty or null
	 * @throws SQLException if SQL query fails
	 */
	public static void delPost(int id, int postType) throws RescueMeException {
		PreparedStatement statement = null;
		Connection connection = null;
		try {
			connection = InitListener.getConnection();
			connection.setAutoCommit(false);

			// Delete post.
			// Note: all linked posts will be deleted as well
			String query = postType == 0 ? "DELETE FROM tasks WHERE taskid = ?" : 
				"DELETE FROM teams WHERE teamid = ?";
			statement = connection.prepareStatement(query);
			statement.setInt(1, id);
			statement.executeUpdate();
			connection.createStatement().execute("commit");
		} catch (SQLException e) {
			e.printStackTrace();
			throw new RescueMeException("SQLException", sqlerr);
		} finally {
			try { statement.close(); }
			catch (SQLException e) { e.printStackTrace(); throw new RescueMeException("SQLException", sqlerr); }
			if(connection != null) { try { connection.close(); } catch (SQLException e) { e.printStackTrace(); } }
		}
	}
	
	/**
	 * Updates a user in the database
	 * @param user - the user to update
	 * @throws RescueMeException if the user is not legal (missing basic information)
	 * @throws SQLException if SQL query fails
	 */
	public static void updateUser(User user) throws RescueMeException {
		if(user.getUserName().isEmpty()) {
			throw new RescueMeException("illegal parameter", "User is missing username");
		}
		PreparedStatement statement = null;
		ResultSet rs = null;
		Connection connection = null;

		try {
			connection = InitListener.getConnection();
			connection.setAutoCommit(false);
			connection.createStatement().execute("begin");
			//check that the user is in the database
			statement = connection.prepareStatement("SELECT username FROM users WHERE username=?");
			statement.setString(1, user.getUserName());
			rs = statement.executeQuery();
			if (rs.next() != true) {
				throw new RescueMeException("userNotExists", "The user you are trying to update doesn't exist");
			}
			rs.close(); rs = null;
			statement.close(); statement = null;
			String password = user.getPassword();
			if(password == null || password.isEmpty()) { // the user doesn't want to change the password
				statement = connection.prepareStatement("UPDATE users SET firstname=?, lastname=?, email=?, phone=?, isAdmin=?" + 
						" WHERE username=?");
				statement.setString(6, user.getUserName());
			} else {
				// the user wants to change the password
				statement = connection.prepareStatement("UPDATE users SET firstname=?, lastname=?, email=?, phone=?, isAdmin=?," +
						" password=? WHERE username=?");
				statement.setString(6, password);
				statement.setString(7, user.getUserName());
			}
			statement.setString(1, user.getFirstName());
			statement.setString(2, user.getLastName());
			statement.setString(3, user.getEmail());
			statement.setString(4, user.getPhone());
			statement.setBoolean(5, user.isAdmin());
			// execute and commit changes
			statement.executeUpdate();
			connection.createStatement().execute("commit");
		} catch (SQLException e) {
			try { connection.createStatement().execute("rollback"); }
			catch (SQLException e1) { e1.printStackTrace(); }
			e.printStackTrace();
			throw new RescueMeException("SQLException", sqlerr);
		} finally {
			try { if(rs != null) { rs.close(); } } catch (SQLException e) { e.printStackTrace(); }
			try { if(statement != null) { statement.close(); } } catch (SQLException e) { e.printStackTrace(); }
			if(connection != null) { try { connection.close(); } catch (SQLException e) { e.printStackTrace(); } }
		}
	}
	
	/**
	 * Get username and a password, and check if this is a valid username-password combination to login
	 * 
	 * @param userName
	 * @param password
	 *            
	 * @return true if there is a user with the password in the database and false if not
	 */
	public static boolean verifyLogin(String userName, String password) {
		ResultSet rs = null;
		PreparedStatement statement = null;
		Connection connection = null;
		try {
			connection = InitListener.getConnection();
			connection.setAutoCommit(false);
			
			statement = connection.prepareStatement("SELECT username FROM users WHERE username=? AND password=?");
			statement.setString(1, userName);
			statement.setString(2, password);
			rs = statement.executeQuery();
	
			// check if there is selection
			if(rs.next()) {connection.createStatement().execute("commit"); return true; }
			connection.createStatement().execute("commit");
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try { if(rs != null) { rs.close(); } } catch (SQLException e) { e.printStackTrace(); }
			try { if(statement != null) { statement.close(); } } catch (SQLException e) { e.printStackTrace(); }
			try { if(connection != null) { connection.close(); } } catch (SQLException e) { e.printStackTrace(); }
		}
		return false;
	}

	/**
	 * Returns a user with all saved details
	 * @param userName
	 * @return User object or null in case of failure
	 * @throws RescueMeException - if basic information like userName or password is missing
	 */
	public static User getUserDetails(String userName) throws RescueMeException {
		ResultSet rs = null;
		PreparedStatement statement = null;
		Connection connection = null;
		
		try {
			connection = InitListener.getConnection();
			connection.setAutoCommit(false);
			statement = connection.prepareStatement("SELECT firstname, lastname, email, phone, " +
					"isAdmin FROM users WHERE username=?");
			statement.setString(1, userName);
			rs = statement.executeQuery();
			if(!rs.next()) { return null; }
			User user = new User(userName, "", rs.getString("firstname"), rs.getString("lastname"), 
					rs.getString("email"), rs.getString("phone"), rs.getBoolean("isAdmin"));
			connection.createStatement().execute("commit");
			return user;
		} catch (SQLException e) {
			e.printStackTrace();
			throw new RescueMeException("SQLException", sqlerr);
		} finally {
			try { if(rs != null) { rs.close(); } } catch (SQLException e) { e.printStackTrace(); }
			try { if(statement != null) { statement.close(); } } catch (SQLException e) { e.printStackTrace(); }
			try { if(connection != null) { connection.close(); } } catch (SQLException e) { e.printStackTrace(); }
		}
	}
	
	/**
	 * Returns a task (mission) of the given Id
	 * 
	 * @param missionID the mission ID
	 * @return the desired mission or null if no such mission
	 */
	public static Mission getMission(int missionID) {
		ResultSet rs = null, rs2 = null;
		PreparedStatement statement = null, statement2 = null;
		Connection connection = null;
		try {
			connection = InitListener.getConnection();
			connection.setAutoCommit(false);
			connection.createStatement().execute("begin");
			statement = connection.prepareStatement("SELECT ownername, creationtime, taskname," +
					"capacity, content, latnum, longnum, skills FROM tasks WHERE taskid=?");
			statement.setInt(1, missionID);
			rs = statement.executeQuery();
			if(!rs.next()) { return null; }
			// get skills from DB
			ArrayList<Skill> allSkills = getSkills(false);
			
			//create missions objects
			Skill[] sa = new Skill[0];	// a dummy array for creating skills arrays
			ArrayList<Skill> missionSkills = new ArrayList<Skill>(0);	// to hold the mission's skills
			//create skills array for the team
			Integer[] skillsIDArray =(Integer[])(rs.getArray("skills").getArray());
			for(int skillID : skillsIDArray) {
				missionSkills.add(Skill.findSkillById(allSkills, skillID));
			}
			// get the teams that was recruited the mission
			statement2 = connection.prepareStatement("SELECT teamid FROM matches WHERE taskid=" + missionID);
			rs2 = statement2.executeQuery();
			ArrayList<Team> membersList = new ArrayList<Team>();
			int teamId;
			while(rs2.next()) {
				teamId = rs2.getInt("teamid");
				membersList.add(getTeam(teamId));
			}
			Team[] members = membersList.toArray(new Team[membersList.size()]);
			connection.createStatement().execute("commit");
						
						
			return new Mission(missionID, rs.getString("ownername"), rs.getDate("creationtime"), 
					rs.getString("taskname"),rs.getInt("capacity"), rs.getString("content"),
					rs.getDouble("latnum"), rs.getDouble("longnum"),
					missionSkills.toArray(sa), members); 
		} catch (SQLException e) { 
			e.printStackTrace();
			return null;
		}
		  catch (RescueMeException e) { 
		  	e.printStackTrace();
		  	return null;
	  	}
		finally {
			try { if(rs != null) { rs.close(); } } catch (SQLException e) { e.printStackTrace(); }
			try { if(rs2 != null) { rs2.close(); } } catch (SQLException e) { e.printStackTrace(); }
			try { if(statement != null) { statement.close(); } } catch (SQLException e) { e.printStackTrace(); }
			try { if(statement2 != null) { statement2.close(); } } catch (SQLException e) { e.printStackTrace(); }
			try { if(connection != null) { connection.close(); } } catch (SQLException e) { e.printStackTrace(); }
		}
	}
	
	/**
	 * Returns a team of the given Id
	 * 
	 * @param teamID the team ID
	 * @return the desired team or null if no such team
	 */
	public static Team getTeam(int teamID) {
		ResultSet rs = null, rs2 = null;
		PreparedStatement statement = null, statement2 = null;
		Connection connection = null;
		try {
			connection = InitListener.getConnection();
			connection.setAutoCommit(false);
			connection.createStatement().execute("begin");
			statement = connection.prepareStatement("SELECT ownername, creationtime, teamname, capacity, content, latnum, longnum, " +
					"skills, team_limit FROM teams WHERE teamid=?");
			statement.setInt(1, teamID);
			rs = statement.executeQuery();
			if(!rs.next()) { return null; }
			// get skills from DB
			ArrayList<Skill> allSkills = getSkills(false);
			//create team objects
			Skill[] sa = new Skill[0];	// a dummy array for creating skills arrays
			ArrayList<Skill> teamSkills = new ArrayList<Skill>(0);	// to hold the team's skills
			//create skills array for the team
			Integer[] skillsIDArray =(Integer[])(rs.getArray("skills").getArray());
			for(int skillID : skillsIDArray) {
				teamSkills.add(Skill.findSkillById(allSkills, skillID));
			}
			// get the members of the team
			statement2 = connection.prepareStatement("SELECT username FROM user_in_team WHERE teamid=? FOR SHARE");
			statement2.setInt(1, teamID);
			rs2 = statement2.executeQuery();
			ArrayList<User> membersList = new ArrayList<User>();
			String username;
			while(rs2.next()) {
				username = rs2.getString("username");
				membersList.add(getUserDetails(username));
			}
			User[] members = membersList.toArray(new User[membersList.size()]);
			connection.createStatement().execute("commit");
			return new Team(teamID, rs.getString("ownername"), rs.getDate("creationtime"), rs.getString("teamname"),
					rs.getInt("capacity"), rs.getString("content"), rs.getDouble("latnum"), rs.getDouble("longnum"),
					teamSkills.toArray(sa), rs.getInt("team_limit"), members);
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
		  catch (RescueMeException e) { 
		  	e.printStackTrace();
		  	return null;
	  	}
		finally {
			try { if(rs != null) { rs.close(); } } catch (SQLException e) { e.printStackTrace(); }
			try { if(rs2 != null) { rs2.close(); } } catch (SQLException e) { e.printStackTrace(); }
			try { if(statement != null) { statement.close(); } } catch (SQLException e) { e.printStackTrace(); }
			try { if(statement2 != null) { statement2.close(); } } catch (SQLException e) { e.printStackTrace(); }
			try { if(connection != null) { connection.close(); } } catch (SQLException e) { e.printStackTrace(); }
		}
	}
	
	/**
	 * Get the list of all the skills
	 * 
	 * @param onlyAvailable true to bring only the available skills (not disabled)
	 *            
	 * @return true if there is a user with the password in the database and false if not
	 */
	public static ArrayList<Skill> getSkills(boolean onlyAvailable) {
		ResultSet rs = null;
		PreparedStatement statement = null;
		Connection connection = null;
		ArrayList<Skill> skills = new ArrayList<Skill>();
		try {
			connection = InitListener.getConnection();
			connection.setAutoCommit(false);
			if(onlyAvailable)
				statement = connection.prepareStatement("SELECT sid, skillname, available from skills WHERE available=true");
			else
				statement = connection.prepareStatement("SELECT sid, skillname, available from skills");
			rs = statement.executeQuery();
			// get all skills
			while(rs.next()) { skills.add(new Skill(rs.getInt("sid"), rs.getString("skillname"), rs.getBoolean("available"))); }
			connection.createStatement().execute("commit");
		} catch (SQLException e) { e.printStackTrace(); }
		finally {
			try { if(rs != null) { rs.close(); } } catch (SQLException e) { e.printStackTrace(); }
			try { if(statement != null) { statement.close(); } } catch (SQLException e) { e.printStackTrace(); }
			try { if(connection != null) { connection.close(); } } catch (SQLException e) { e.printStackTrace(); }
		}
		return skills;
	}

	
	/**
	 * @return a list of all the users in the system
	 */
	public static ArrayList<User> getAllUsers() {		
		ResultSet rs = null;
		PreparedStatement statement = null;
		Connection connection = null;
		ArrayList<User> users = new ArrayList<User>();
		try {
			connection = InitListener.getConnection();
			connection.setAutoCommit(false);
			statement = connection.prepareStatement("SELECT username, firstname, lastname, email, phone, isAdmin FROM users");
			rs = statement.executeQuery();
			while(rs.next() == true) {
				users.add(new User(rs.getString("username"), "", rs.getString("firstname"), rs.getString("lastname"), 
						rs.getString("email"), rs.getString("phone"), rs.getBoolean("isAdmin")));
			}
			connection.createStatement().execute("commit");
		} catch (SQLException e) { e.printStackTrace(); }
		catch (RescueMeException e) { e.printStackTrace(); }
		finally {
			try { if(rs != null) { rs.close(); } } catch (SQLException e) { e.printStackTrace(); }
			try { if(statement != null) { statement.close(); } } catch (SQLException e) { e.printStackTrace(); }
			try { if(connection != null) { connection.close(); } } catch (SQLException e) { e.printStackTrace(); }
		}
		return users;
	}
	
	/**
	 * give a list of teams of a specific owner if owner is given,
	 * if owner is null then all the teams in the system are returned
	 * 
	 * @param owner (optional) owner of the groups
	 * @return a list of all the rescue teams.
	 */
	public static ArrayList<Post> getAllTeams(String owner) {
		ResultSet rs = null;
		PreparedStatement statement = null;
		Connection connection = null;
		ArrayList<Post> teams = new ArrayList<Post>();
		try {
			connection = InitListener.getConnection();
			connection.setAutoCommit(false);
			if(owner == null || owner.equals("")) {
				statement = connection.prepareStatement("SELECT teamid, ownername, creationtime, teamname, " +
						"capacity, content, latnum, longnum, skills, team_limit FROM teams");
			} else {
				statement = connection.prepareStatement("SELECT teamid, ownername, creationtime, teamname, " +
						"capacity, content, latnum, longnum, skills, team_limit FROM teams WHERE ownername=?");
				statement.setString(1, owner);
			}
			rs = statement.executeQuery();
			
			// get skills from DB
			ArrayList<Skill> allSkills = getSkills(false);

			//create teams objects
			Skill[] sa = new Skill[0];	// a dummy array for creating skills arrays
			ArrayList<Skill> teamSkills = new ArrayList<Skill>(0);	// to hold the team's skills
			while(rs.next() == true) {
				//create skills array for the team
				teamSkills.clear();
				Integer[] skillsIDArray =(Integer[])rs.getArray("skills").getArray();
				for(int skillID : skillsIDArray){
					Skill s = Skill.findSkillById(allSkills, skillID);
					if(s != null){
						teamSkills.add(s);
					}
				}
				teams.add(new Team(rs.getInt("teamid"), rs.getString("ownername"), rs.getDate("creationtime"), rs.getString("teamname"),
						rs.getInt("capacity"), rs.getString("content"), rs.getDouble("latnum"),
						rs.getDouble("longnum"), teamSkills.toArray(sa), rs.getInt("team_limit")));
			}
			connection.createStatement().execute("commit");
		} catch (SQLException e) { e.printStackTrace(); }
		  catch (RescueMeException e) { e.printStackTrace(); }
		finally {
			try { if(rs != null) { rs.close(); } } catch (SQLException e) { e.printStackTrace(); }
			try { if(statement != null) { statement.close(); } } catch (SQLException e) { e.printStackTrace(); }
			try { if(connection != null) { connection.close(); } } catch (SQLException e) { e.printStackTrace(); }
		}
		return teams;
	}
	
	public static ArrayList<Post> getAllTeams() {
		return getAllTeams(null);
	}
	
	/**
	 * give a list of missions of a specific owner if owner is given,
	 * if owner is null then all the missions in the system are returned
	 * 
	 * @param owner (optional) owner of the mission
	 * @return a list of all the rescue mission.
	 */
	public static ArrayList<Post> getAllMissions(String owner) {
		ResultSet rs = null;
		PreparedStatement statement = null;
		Connection connection = null;
		ArrayList<Post> missions = new ArrayList<Post>();
		
		try {
			connection = InitListener.getConnection();
			connection.setAutoCommit(false);
			
			if(owner == null || owner.equals("")) {
				statement = connection.prepareStatement("SELECT taskid, ownername, creationtime, taskname, " +
						"capacity, content, latnum, longnum, skills FROM tasks");
			} else {
				statement = connection.prepareStatement("SELECT taskid, ownername, creationtime, taskname, " +
						"capacity, content, latnum, longnum, skills FROM tasks WHERE ownername=?");
				statement.setString(1, owner);
			}
			rs = statement.executeQuery();
			
			// get skills from DB
			ArrayList<Skill> allSkills = getSkills(false);
			
			//create missions objects
			Skill[] sa = new Skill[0];	// a dummy array for creating skills arrays
			ArrayList<Skill> missionSkills = new ArrayList<Skill>(0);	// to hold the mission's skills
			while(rs.next() == true) {
				//create skills array for the team
				missionSkills.clear();
				Integer[] skillsIDArray =(Integer[])rs.getArray("skills").getArray();
				for(int skillID : skillsIDArray){
					Skill s = Skill.findSkillById(allSkills, skillID);
					if (s != null){
						missionSkills.add(s);
					}
				}
				missions.add(new Mission(rs.getInt("taskid"), rs.getString("ownername"), rs.getDate("creationtime"), rs.getString("taskname"),
						rs.getInt("capacity"), rs.getString("content"), rs.getDouble("latnum"), rs.getDouble("longnum"),
						missionSkills.toArray(sa)));
				connection.createStatement().execute("commit");
			}
		} catch (SQLException e) { e.printStackTrace(); }
		  catch (RescueMeException e) { e.printStackTrace(); }
		finally {
			try { if(rs != null) { rs.close(); } } catch (SQLException e) { e.printStackTrace(); }
			try { if(statement != null) { statement.close(); } } catch (SQLException e) { e.printStackTrace(); }
			try { if(connection != null) { connection.close(); } } catch (SQLException e) { e.printStackTrace(); }
		}
		return missions;
	}
	
	public static ArrayList<Post> getAllMissions() {
		return getAllMissions(null);
	}
	
	/**
	 * Returns a list of all matches between the specified missions and teams.
	 * If missions list is empty or null, then all matches that contain a team from teams list will be returned.
	 * If teams list is empty or null then all matches that contain a mission from missions list will be returned.  
	 * If both lists are empty, all matches in the DB are returned.
	 * 
	 * @param missions - the missions we want to find matches for
	 * @param teams - the teams we want to find matches for
	 * 
	 * @return A list of the relevant matches. Every member in the list is an array of two cells.
	 * The first cell contains the id of the matched task and the second contains the id of the matched team:
	 * [matchedTaskID, matchedTeamID]
	 */
	public static ArrayList<int[]> getMatches(ArrayList<Post> missions, ArrayList<Post> teams) {
		ResultSet rs = null;
		PreparedStatement statement = null;
		Connection connection = null;
		ArrayList<int[]> matches = new ArrayList<int[]>();
		
		try {
			connection = InitListener.getConnection();
			connection.setAutoCommit(false);
			
			String strStatement="";
			
			if(missions != null && teams != null && !missions.isEmpty() && !teams.isEmpty()) {
				strStatement = "SELECT taskid, teamid FROM matches WHERE taskid IN (" +
						Post.getIDsString(missions) + ") and teamid IN(" + Post.getIDsString(teams) + ")";
			} else if (missions != null && !missions.isEmpty()) {
				strStatement = "SELECT taskid, teamid FROM matches WHERE taskid IN (" +
						Post.getIDsString(missions) + ")";
			} else if (teams != null && !teams.isEmpty()) {
				strStatement = "SELECT taskid, teamid FROM matches WHERE teamid IN (" +
						Post.getIDsString(teams) + ")";
			} else {
				strStatement = "SELECT taskid, teamid FROM matches";
			}
			statement = connection.prepareStatement(strStatement);
			rs = statement.executeQuery();
			
			while(rs.next()) {
				//create skills array for the team
				int[] match = new int[2];
				match[0]=rs.getInt("taskid");
				match[1]=rs.getInt("teamid");
				matches.add(match);
			}
			connection.createStatement().execute("commit");
		} catch (SQLException e) { e.printStackTrace(); }
		finally {
			try { if(rs != null) { rs.close(); } } catch (SQLException e) { e.printStackTrace(); }
			try { if(statement != null) { statement.close(); } } catch (SQLException e) { e.printStackTrace(); }
			try { if(connection != null) { connection.close(); } } catch (SQLException e) { e.printStackTrace(); }
		}
		return matches;
	}
	
	
	/**
	 * Returns all tasks that are within radius distance around point loc
	 * @param latPos - latitude of the middle of the circle
	 * @param lngPos - longitude of the middle of the circle
	 * @param distance -  the radius
	 * @param username - null or empty string brings tasks of all users. if not empty- the function will return only
	 * tasks that were created by this user.
	 * @param resultType- 0 to return tasks, 1 to return teams, 2 to return both. 
	 * 
	 * @return An array list of all founded tasks or null on error
	 */
	public static ArrayList<Post> searchPosts(Double latPos , Double lngPos, Double distance,
			int resultType, String username) {
		switch(resultType){
		case 0:
		case 1:
			return searchPostsWrapped(latPos , lngPos, distance, resultType, username);
		case 2:
			ArrayList<Post> posts = searchPostsWrapped(latPos , lngPos, distance, 0, username);
			posts.addAll(searchPostsWrapped(latPos , lngPos, distance, 1, username));
			return posts;
		default:
			return searchPostsWrapped(latPos , lngPos, distance, resultType, username);
		}
	}
	
	private static ArrayList<Post> searchPostsWrapped(Double latPos , Double lngPos, Double distance,
			int resultType, String username) {
		ResultSet rs = null;
		PreparedStatement statement = null;
		Connection connection = null;
		ArrayList<Post> postsSet = new ArrayList<Post>();
		String strStatement="";
		
		try {
			connection = InitListener.getConnection();
			connection.setAutoCommit(false);
			 
			switch (resultType) {
			case 0:
				strStatement += "SELECT taskid, ownername, creationtime, taskname, capacity," +
						"content, latnum, longnum, skills FROM tasks WHERE " + 
						"ST_DWithin(geotag,ST_GeographyFromText('POINT(" + latPos + " " + lngPos + ")'),?)";
				break;
			case 1:
				strStatement += "SELECT teamid, ownername, creationtime, teamname, capacity," +
						"content, latnum, longnum, skills, team_limit FROM teams WHERE " + 
						"ST_DWithin(geotag,ST_GeographyFromText('POINT(" + latPos + " " + lngPos + ")'),?)";
				break;
			default: return postsSet;
			}
			
			// if username is not empty, get only messages of a specific user
			if(username != null && !username.equals("")) { strStatement+=" and username=?"; }
			statement = connection.prepareStatement(strStatement);
			statement.setDouble(1, distance);
			if(username != null && !username.equals("")) { statement.setString(2, username); }
			rs = statement.executeQuery();
			// get skills from DB
			ArrayList<Skill> allSkills = getSkills(false);
			// create posts objects
			Skill[] sa = new Skill[0];	// a dummy array for creating skills arrays
			ArrayList<Skill> postSkills = new ArrayList<Skill>(0);	// to hold the post's skills
			switch (resultType) {
			case 0:
				while(rs.next()) {
					//create skills array for the mission
					postSkills.clear();
					Integer[] skillsIDArray =(Integer[])rs.getArray("skills").getArray();
					for(int skillID : skillsIDArray){
						Skill s = Skill.findSkillById(allSkills, skillID);
						if(s != null) { postSkills.add(s); }
					}
					Mission mission = new Mission(rs.getInt("taskid"), rs.getString("ownername"),
							rs.getDate("creationtime"), rs.getString("taskname"),
							rs.getInt("capacity"), rs.getString("content"), rs.getDouble("latnum"), 
							rs.getDouble("longnum"), postSkills.toArray(sa));
					postsSet.add(mission);
				}
				break;
			case 1:
				while(rs.next()) {
					//create skills array for the team
					postSkills.clear();
					Integer[] skillsIDArray =(Integer[])rs.getArray("skills").getArray();
					for(int skillID : skillsIDArray){
						Skill s = Skill.findSkillById(allSkills, skillID);
						if(s != null){ postSkills.add(s); }
					}
					Team team = new Team(rs.getInt("teamid"), rs.getString("ownername"),
							rs.getDate("creationtime"), rs.getString("teamname"),
							rs.getInt("capacity"), rs.getString("content"),
							rs.getDouble("latnum"), rs.getDouble("longnum"), postSkills.toArray(sa), rs.getInt("team_limit"));	
					postsSet.add(team);
				}
				break;
			}
			connection.createStatement().execute("commit");
			return postsSet;
		} catch (SQLException e) {
			try { connection.createStatement().execute("rollback"); }
			catch (SQLException e1) {
				System.err.println("Exception on statement: " + strStatement);
				e1.printStackTrace();
			}
			e.printStackTrace();
			return null;
		} catch (RescueMeException e) {
			System.err.println("Exception on statement: " + strStatement);
			e.printStackTrace();
			return null;
		} finally {
			try { rs.close(); } catch (SQLException e) { e.printStackTrace(); }
			try { statement.close(); } catch (SQLException e) { e.printStackTrace(); }
			try { if(null != connection) { connection.close(); } }
			catch (SQLException e) { e.printStackTrace(); }
		}
	}
	
	
	/**
	 * Updates a user in the database
	 * @param user - the user to update
	 * @throws RescueMeException if the user is not legal (missing basic information)
	 * @throws SQLException if SQL query fails
	 */
	public static void inverseSkillAvailability(int id) throws RescueMeException, SQLException {
		PreparedStatement statement = null;
		ResultSet rs = null;
		Connection connection = null;

		try {
			connection = InitListener.getConnection();
			connection.setAutoCommit(false);
			// start transaction
			connection.createStatement().execute("begin");
			statement = connection.prepareStatement("SELECT available from skills WHERE sid=?");
			statement.setInt(1, id);
			rs = statement.executeQuery();
			if(!rs.next()) {
				throw new RescueMeException("illigle parameter", "skill id " + id + " not exists in the system");
			}
			boolean available = rs.getBoolean("available");
			available = !available;
			rs.close();
			rs = null;
			statement.close();
			statement = connection.prepareStatement("UPDATE skills SET available=? WHERE sid=?");
			statement.setBoolean(1, available);
			statement.setInt(2, id);
			statement.executeUpdate();
			connection.createStatement().execute("commit");
		} catch (SQLException e) {
			try { connection.createStatement().execute("rollback"); }
			catch (SQLException e1) { e1.printStackTrace(); }
			throw e;
		} finally {
			try { if(rs != null) { rs.close(); } } catch (SQLException e) { e.printStackTrace(); }
			try { if(statement != null) { statement.close(); } } catch (SQLException e) { e.printStackTrace(); }
			try { if(connection != null) { connection.close(); } } catch (SQLException e) { e.printStackTrace(); }
		}
	}
	
	public static void addUserToTeam(int teamId, String username) throws RescueMeException {
		PreparedStatement statement = null;
		ResultSet rs = null;
		Connection connection = null;
		try {
			connection = InitListener.getConnection();
			connection.setAutoCommit(false);
			connection.createStatement().execute("begin");
			statement = connection.prepareStatement("SELECT firstname from users WHERE username=? FOR SHARE");
			statement.setString(1, username);
			rs = statement.executeQuery();
			if(!rs.next()) {
				throw new RescueMeException("illigle parameter", "username: " + username + "dosen't exists");
			}
			rs.close(); rs = null;
			statement.close(); statement = null;
			statement = connection.prepareStatement("SELECT teamid FROM teams WHERE teamid=" + teamId + " FOR SHARE");
			rs = statement.executeQuery();
			if(!rs.next()) {
				throw new RescueMeException("illigle parameter", "team with id " + teamId + " dosen't exists");
			}
			rs.close(); rs = null;
			statement.close(); statement = null;
			statement = connection.prepareStatement("INSERT INTO user_in_team(teamid, username) VALUES(" + teamId + ", ?)");
			statement.setString(1, username);
			statement.executeUpdate();
			connection.createStatement().execute("commit");
		} catch (SQLException e) {
			try {
				connection.createStatement().execute("rollback");
			} catch (SQLException e1) { e1.printStackTrace(); }
			e.printStackTrace();
			throw new RescueMeException("SQLException", sqlerr);
		} finally {
			try { if(rs != null) { rs.close(); } } catch (SQLException e) { e.printStackTrace(); }
			try { if(statement != null) { statement.close(); } } catch (SQLException e) { e.printStackTrace(); }
			try { if(connection != null) { connection.close(); } } catch (SQLException e) { e.printStackTrace(); }
		}
	}
	
	public static void matchTeamToMission(int teamId, int taskId) throws RescueMeException {
		PreparedStatement statement = null;
		ResultSet rs = null;
		Connection connection = null;
		try {
			connection = InitListener.getConnection();
			connection.setAutoCommit(false);
			connection.createStatement().execute("begin");
			statement = connection.prepareStatement("SELECT teamid from teams WHERE teamid=" + teamId + " FOR SHARE");
			rs = statement.executeQuery();
			if(!rs.next()) {
				throw new RescueMeException("illigle parameter", "Team with id " + teamId + " dosen't exists");
			}
			rs.close(); rs = null;
			statement.close(); statement = null;
			statement = connection.prepareStatement("SELECT taskid FROM tasks WHERE taskid=" + taskId + " FOR SHARE");
			rs = statement.executeQuery();
			if(!rs.next()) {
				throw new RescueMeException("illigle parameter", "Task with id " + taskId + " dosen't exists");
			}
			rs.close(); rs = null;
			statement.close(); statement = null;
			statement = connection.prepareStatement("INSERT INTO matches(teamid, taskid) VALUES(" + teamId + ", " + taskId + ")");
			statement.executeUpdate();
			connection.createStatement().execute("commit");
		} catch (SQLException e) {
			try {
				connection.createStatement().execute("rollback");
			} catch (SQLException e1) { e1.printStackTrace(); }
			e.printStackTrace();
			throw new RescueMeException("SQLException", sqlerr);
		} finally {
			try { if(rs != null) { rs.close(); } } catch (SQLException e) { e.printStackTrace(); }
			try { if(statement != null) { statement.close(); } } catch (SQLException e) { e.printStackTrace(); }
			try { if(connection != null) { connection.close(); } } catch (SQLException e) { e.printStackTrace(); }
		}
	}
	
	public static void removeUserFromTeame(int teamId, String username) throws RescueMeException {
		PreparedStatement statement = null;
		Connection connection = null;
		try {
			connection = InitListener.getConnection();
			connection.setAutoCommit(false);
			connection.createStatement().execute("begin");
			statement = connection.prepareStatement("DELETE FROM user_in_team WHERE username=? AND teamid=" + teamId);
			statement.setString(1, username);
			statement.executeUpdate();
			connection.createStatement().execute("commit");
		} catch (SQLException e) {
			try {
				connection.createStatement().execute("rollback");
			} catch (SQLException e1) { e1.printStackTrace(); }
			e.printStackTrace();
			throw new RescueMeException("SQLException", sqlerr);
		} finally {
			try { if(statement != null) { statement.close(); } } catch (SQLException e) { e.printStackTrace(); }
			try { if(connection != null) { connection.close(); } } catch (SQLException e) { e.printStackTrace(); }
		}
	}

	public static final String sqlerr = "Some errore have occured with the site's database.\n" +
			"Please contact system administrator";
}
